﻿using System;
using System.Data.SqlClient;

class ATM
{
    static void Main()
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = "Server=.\\SQLEXPRESS; " +
        "Database=SATM; Integrated Security=true";
        con.Open();
        using (con)
        {
            //CreateCardAccountTable(con);
            //InsertValuesIntoCardAccounts(con);
            WithDrawMoney(con, "1234567890", "1234", 12);
            //CreateAccountTransactionsTable(con);
            GetHistoryForCard(con, "1234567890", "1224");
        }
    }

    public static void CreateCardAccountTable(SqlConnection con)
    {
        SqlCommand createCommand = new SqlCommand();
        createCommand.Connection = con;
        createCommand.CommandText = "CREATE TABLE CardAccounts " +
                                    "(" +
                                    "CardAccountID int identity primary key ," +
                                    "CardNumber char(10) not null unique ," +
                                    "CardPIN char(4) not null ," +
                                    "CardCash money not null " +
                                    ")";
        createCommand.ExecuteNonQuery();
        Console.WriteLine("created CardAccounts");
    }

    public static void CreateAccountTransactionsTable(SqlConnection con)
    {
        SqlCommand createCommand = new SqlCommand();
        createCommand.Connection = con;
        createCommand.CommandText = "CREATE TABLE AccountTransactions " +
                                    "(" +
                                    "AccountTransactionID int identity primary key ," +
                                    "CardNumber char(10) not null ," +
                                    "TransactionDate datetime not null ," +
                                    "Amount money not null " +
                                    ")";
        createCommand.ExecuteNonQuery();
        Console.WriteLine("created AccountTransactions");
    }

    public static void InsertValuesIntoCardAccounts(SqlConnection con)
    {
        SqlCommand createCommand = new SqlCommand();
        createCommand.Connection = con;
        createCommand.CommandText = "INSERT INTO CardAccounts VALUES " +
                                    "(@number,@PIN,@money)";
        createCommand.Parameters.AddWithValue("@number", null);
        createCommand.Parameters.AddWithValue("@PIN", null);
        createCommand.Parameters.AddWithValue("@money", null);
        Random generator = new Random();
        for (int i = 0; i < 10000; i++)
        {
            createCommand.Parameters["@number"] = new SqlParameter("@number", generator.Next(1000000000, int.MaxValue));
            createCommand.Parameters["@PIN"] = new SqlParameter("@PIN", generator.Next(1000, 10000));
            createCommand.Parameters["@money"] = new SqlParameter("@money", generator.Next(0, int.MaxValue));
            createCommand.ExecuteNonQuery();
        }
        Console.WriteLine("inserted");
    }

    public static void WithDrawMoney(SqlConnection con, string targetID, string pin, int amount)
    {
        SqlCommand withDrawCommand = new SqlCommand();
        withDrawCommand.Connection = con;
        SqlTransaction withDrawTrans = con.BeginTransaction();
        withDrawCommand.Transaction = withDrawTrans;

        try
        {
            withDrawCommand.CommandText = "select CardCash from CardAccounts " +
                "where CardNumber = @inputID and CardPIN =@pin";
            withDrawCommand.Parameters.AddWithValue("@inputID", targetID);
            withDrawCommand.Parameters.AddWithValue("@pin", pin);
            decimal moneyBeforeTrans = (decimal)withDrawCommand.ExecuteScalar();
            if (moneyBeforeTrans < amount)
            {
                withDrawTrans.Rollback();
                Console.WriteLine("Not Enough Money! Transaction Ended!");
                return;
            }
            withDrawCommand.CommandText = "update CardAccounts " +
                "set CardCash = CardCash - @amount " +
                "where CardNumber = @inputID";
            withDrawCommand.Parameters.AddWithValue("@amount", amount);
            withDrawCommand.ExecuteNonQuery();
            withDrawCommand.CommandText = "insert into AccountTransactions " +
                "values (@inputID,@dateNow,-@amount)";
            withDrawCommand.Parameters.AddWithValue("@dateNow", DateTime.Now);
            withDrawCommand.ExecuteNonQuery();
            withDrawTrans.Commit();
            Console.WriteLine("Successfully charged!");
        }
        catch
        {
            withDrawTrans.Rollback();
            Console.WriteLine("Transaction Cancelled");
            throw;
        }
    }

    public static void GetHistoryForCard(SqlConnection con, string cardNumber, string pin)
    {
        SqlCommand commandHistory = new SqlCommand();
        commandHistory.Connection = con;
        commandHistory.CommandText = "select COUNT(*) " +
            "from CardAccounts " +
            "where @CardNumber=CardNumber and CardPIN=@pin";
        commandHistory.Parameters.AddWithValue("@CardNumber", cardNumber);
        commandHistory.Parameters.AddWithValue("@pin", pin);
        int count = (int)commandHistory.ExecuteScalar();
        if (count != 1)
        {
            throw new InvalidOperationException("The CardNumber is not correct");
        }
        commandHistory.CommandText = "select TransactionDate,Amount " +
            "from AccountTransactions " +
            "where @CardNumber=CardNumber";
        SqlDataReader reader = commandHistory.ExecuteReader();

        while (reader.Read())
        {
            Console.WriteLine("DATE:{0}\t MONEY:{1}", reader.GetDateTime(0), reader["Amount"]);
        }
    }
}

